#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/10/31 15:20
# @Author  : 王凯
# @File    : clean_to_result.py
# @Project : scrapy_spider
import datetime
import re

import dateutil
import numpy as np
import pandas as pd
import warnings

from dateutil.relativedelta import relativedelta
from loguru import logger

from apps.pdd.pdd.clean import TABLE_NET_PDD_SHOP_INFO

warnings.filterwarnings("ignore")
from components.config import WFQ_SOURCE_MYSQL_CONFIG
from utils.db.mysqldb import MysqlDB

RESULT_MALL_KEYS = [
    'version', 'platform_id', 'platform_name', 'mall_id', 'mall_name', 'company_name_src', 'taxpayer_id', 'company_name', 'month_sales', 'month_sales_money', 'total_sales',
    'total_sales_money', 'percent_month_sales', 'percent_month_sales_money', 'percent_total_sales', 'percent_total_sales_money', 'goods_num', 'self_support', 'mall_url',
    'mall_open_time', 'mall_open_time_year', 'mall_status', 'mall_end_time', 'mall_platform_classification', 'mall_business_classification', 'mall_scope', 'company_province',
    'company_city', 'company_county', 'company_town', 'mall_location_code', 'mall_send_province', 'mall_send_city', 'mall_send_county', 'mall_send_location_code', 'mall_desc',
    'mall_logo', 'mall_star', 'mall_label', 'mall_rating_text', 'mall_achievements_rank', 'mall_review_num', 'mall_positive_num', 'mall_followers_num', 'mall_service_score',
    'mall_details_label_list', 'create_at', 'update_at',
]

RESULT_GOODS_KEYS = [
    "version", "company_name", "mall_name", "mall_id", "goods_id", "goods_name", "price", "sale_num", "level_1", "level_2", "level_3", "goods_rank", "hd_url", "market_price",
    "normal_price", "tag_list",
]


class CleanToResult:
    def __init__(self, version=None, save_to_db=True):
        self._to_db = None
        if version is None:
            version = datetime.datetime.now().strftime("%Y%m")
        self.version = version
        self.save_to_db = save_to_db

    @property
    def to_db(self):
        if self._to_db is None:
            self._to_db = MysqlDB(
                ip=WFQ_SOURCE_MYSQL_CONFIG["MYSQL_IP"],
                port=WFQ_SOURCE_MYSQL_CONFIG["MYSQL_PORT"],
                db=WFQ_SOURCE_MYSQL_CONFIG["MYSQL_DB"],
                user_name=WFQ_SOURCE_MYSQL_CONFIG["MYSQL_USER_NAME"],
                user_pass=WFQ_SOURCE_MYSQL_CONFIG["MYSQL_USER_PASS"],
            )
        return self._to_db

    def get_datas(self, mall_ids):
        time_str = datetime.datetime.strptime(self.version, "%Y%m").replace(day=1).strftime("%Y-%m-%d")
        mall_ids_str = ",".join([f"'{i}'" for i in mall_ids])
        mall_datas = self.to_db.find(f"select * from {TABLE_NET_PDD_SHOP_INFO} where mall_id in ({mall_ids_str})", to_json=True)
        goods_data = self.to_db.find(f"select * from net_pdd_goods_detail where mall_id in ({mall_ids_str}) and update_date>='{time_str}'", to_json=True)
        mall_df = pd.DataFrame(mall_datas)
        goods_df = pd.DataFrame(goods_data)
        if not goods_df.empty:
            goods_df = goods_df.merge(mall_df[['mall_id', 'shop_name']], on=['mall_id'])
        return mall_df, goods_df

    def run_by_ids(self, mall_ids):
        logger.info(f"计算开始处理数据 {mall_ids}")
        mall_df, goods_df = self.get_datas(mall_ids)
        self.process_mall_base_info(mall_df)
        self.process_goods_info(goods_df)

    def process_mall_base_info(self, df_pro):
        new_df = df_pro.copy()
        new_df.rename(columns={
            'shop_name': 'mall_name',
            'shop_url': 'mall_url',
            'shop_status': 'mall_status',
            'service_score': 'mall_service_score',
            'last_crawler_time': 'update_at',
        }, inplace=True)
        new_df['mall_open_time_year'] = new_df['open_time_label'].map(lambda x: x.split('年')[0] if x else None)
        new_df['mall_label'] = new_df['mall_label_volist'].map(lambda x: ';'.join([i['desc'] for i in x]) if x else None)
        new_df['mall_achievements_rank'] = new_df['mall_label_volist'].map(lambda x: ';'.join([i['desc'] for i in x if '榜第' in i['desc']]) if x else None)
        new_df['mall_rating_text'] = new_df['mall_rating_text_list'].map(
            lambda x: ';'.join([(i['mallRatingKey']['txt'] + ':' + i['mallRatingValue']['txt']) if 'mallRatingKey' in i else (i['mall_rating_key']['txt'] + ':' + i['mall_rating_value']['txt']) for i in x]) if x else None
        )
        result_df = new_df[[i for i in RESULT_MALL_KEYS if i in new_df.columns]]
        result_df['version'] = self.version
        if self.save_to_db:
            result_df.replace({np.nan: None}, inplace=True)
            print(result_df.to_dict('records'))
            self.to_db.add_batch_smart('result_pdd_shop_info', result_df.to_dict('records'), update_columns=RESULT_MALL_KEYS)
        return df_pro

    def _deal_goods_num(self, goods_num_str):
        base = 1
        if not goods_num_str:
            return None
        if '万' in goods_num_str:
            base = 10000

        num = self.reg_one([
            r'.*售([\d.]*)',
            r'.*抢([\d.]*)',
            r'.*拼([\d.]*)',
            r'.*评([\d.]*)',
            r'.*购([\d.]*)',
        ], goods_num_str)
        if num:
            return float(num) * base
        else:
            if not any(c in goods_num_str for c in ['想拼']):
                print(goods_num_str)

    def reg_one(self, regs, text):
        for reg in regs:
            res = re.findall(reg, text)
            if res:
                return res[0]
        return

    def process_goods_info(self, df_pro):
        new_df = df_pro.copy()
        new_df.rename(columns={
            "shop_name": "mall_name"
        }, inplace=True)
        if not new_df.empty:
            new_df['sale_num'] = new_df['sale_num'].replace({np.nan: 0})
            new_df['sale_num'].mask(
                new_df['sale_num'] == 0,
                new_df['sales_tip'].map(
                    lambda x: self._deal_goods_num(x)
                ), inplace=True
            )
            new_df.sort_values(['update_date'], ascending=[True])
            new_df.drop_duplicates(subset=['mall_id', 'goods_id'], keep='last', inplace=True)
            new_df['price'] = new_df['price'].map(lambda x: x / 100)
            new_df['market_price'] = new_df['market_price'].map(lambda x: x / 100)
            new_df['normal_price'] = new_df['normal_price'].map(lambda x: x / 100)
            new_df['sales_money'] = new_df['sale_num'] * new_df['price']
            new_df.groupby(['mall_id']).apply(lambda df_one: self.update_mall_sales_info(df_one))

    def update_mall_sales_info(self, df_pro):
        df_pro['sales_money'].replace({np.nan: 0}, inplace=True)
        df_pro.sort_values(['sales_money'], ascending=[False])
        df_pro['goods_rank'] = df_pro['sales_money'].rank(method='first', ascending=False).astype(int)

        total_sales = df_pro['sale_num'].sum() / 10000
        total_sales_money = df_pro['sales_money'].sum() / 10000

        result_df = df_pro[[i for i in RESULT_GOODS_KEYS if i in df_pro.columns]]
        result_df['version'] = self.version
        result_df.sort_values(['goods_rank'], ascending=[True])
        if self.save_to_db:
            mall_id = result_df['mall_id'].iloc[0]
            last_version = (datetime.datetime.now().replace(day=1) - relativedelta(months=-1)).strftime("%Y%m")
            last_one_mall_infos = self.to_db.find(f"select * from result_pdd_shop_info where mall_id='{mall_id}' and version='{last_version}'", to_json=True)
            if last_one_mall_infos:
                one_mall_info = last_one_mall_infos[0]
                month_sales = one_mall_info['total_sales'] - total_sales
                month_sales_money = one_mall_info['total_sales_money'] - total_sales_money
                percent_month_sales = (one_mall_info['month_sales'] / month_sales) * 100
                percent_month_sales_money = (one_mall_info['month_sales_money'] / month_sales_money) * 100
                percent_total_sales = (one_mall_info['total_sales'] / total_sales) * 100
                percent_total_sales_money = (one_mall_info['total_sales_money'] / total_sales_money) * 100
            else:
                month_sales = total_sales
                month_sales_money = total_sales_money
                percent_month_sales = 100
                percent_month_sales_money = 100
                percent_total_sales = 100
                percent_total_sales_money = 100

            self.to_db.update_smart('result_pdd_shop_info', {
                'total_sales': total_sales,
                'goods_num': df_pro.shape[0],
                'total_sales_money': total_sales_money,
                'month_sales': month_sales,
                'month_sales_money': month_sales_money,
                'percent_month_sales': round(percent_month_sales, 4),
                'percent_month_sales_money': round(percent_month_sales_money, 4),
                'percent_total_sales': round(percent_total_sales, 4),
                'percent_total_sales_money': round(percent_total_sales_money, 4),
            }, f"mall_id='{mall_id}' and version='{self.version}'")
            result_df.replace({np.nan: None}, inplace=True)
            self.to_db.add_batch_smart('result_pdd_goods_detail', result_df.to_dict('records'), update_columns=RESULT_GOODS_KEYS)

    def run(self):
        pass


if __name__ == '__main__':
    CleanToResult().run_by_ids(['100007614'])
    # CleanToResult().run()
